<?php
/**
 * Created by PhpStorm.
 * Author: tu shengxiang
 * Date: 2018/6/20
 * Time: 15:06
 */
namespace App\Admin\Models;
use Illuminate\Database\Eloquent\Model;
use DB;
use Redis;


class MonthlyStatistic extends Model {

    protected $table = 'monthly_statistic';
    protected $fillable = [
        'month','share_num','view_num','access_num',
    'task_num','processing_task','recharge_sum', 'recharge_num',
    'recharge_before_sum','recharge_before_num','register_num'
    ];

    public static function getDayShareNum($date)
    {
        $start_date=$date.'00:00:00';
        $end_date = date('Y-m-d 23:59:59',strtotime(date("Y-m-01",strtotime($date.' +1 month')).' -1 day'));
        $raw_data = DB::select("select count(id) as cid from `spreads` where `created_at`>= '".$start_date."' and `created_at` <= '".$end_date." 23:59:59' limit 1");
        $res = $raw_data[0]->cid;

        return $res;
    }

    public static function getRegisterNum($date)
    {
        $start_date=$date.'00:00:00';
        $end_date = date('Y-m-d 23:59:59',strtotime(date("Y-m-01",strtotime($date.' +1 month')).' -1 day'));
        $raw_data = DB::select("select count(id) as cid from users where nickname is not null and head_img is not null and updated_at >='". $start_date."' AND updated_at <='".$end_date."'");
        $res = $raw_data[0]->cid;

        return $res;
    }

    /**
     * 獲取某日任務數
     * @param $date
     * @return mixed
     */
    public static function getTaskNnum($date) {
        $start_date=$date.'00:00:00';
        $end_date = date('Y-m-d 23:59:59',strtotime(date("Y-m-01",strtotime($date.' +1 month')).' -1 day'));
        $raw_data = DB::select("select count(id) as cid from `tasks` where `created_at`>= '".$start_date."' and `created_at` <= '".$end_date." 23:59:59' limit 1");
        $res = $raw_data[0]->cid;

        return $res;
    }

    /**
     * 獲取正在進行的任務數
     * @param $date
     * @return mixed
     */
    public static function getProcTask($date) {
        $start_date=$date.'00:00:00';
        $end_date = date('Y-m-d 23:59:59',strtotime(date("Y-m-01",strtotime($date.' +1 month')).' -1 day'));
        $now_date = $date.'00:00:00';
        $raw_data = DB::select("SELECT count(id) as proc_num FROM tasks tks WHERE tks.end_time<= '{$end_date}' AND (EXISTS (SELECT * FROM (select COUNT(temp.id) AS count_chain,temp.id from (
	select t.id,srsc.id as srscid from `tasks` t
		INNER JOIN `self_reco_suc_chains` srsc on srsc.task_id = t.id 
		where t.`end_time`<= '{$now_date}'
) temp GROUP BY temp.id) final WHERE final.id = tks.id AND tks.target_user_num > final.count_chain) OR NOT EXISTS (SELECT * FROM `self_reco_suc_chains` srsc2 WHERE srsc2.task_id = tks.id))
");
        $res = $raw_data[0]->proc_num;

        return $res;
    }

    /**
     *
     * @param $date
     * @return mixed
     */
    public static function getRechargeSum($date) {
        $start_date=$date.'00:00:00';
        $end_date = date('Y-m-d 23:59:59',strtotime(date("Y-m-01",strtotime($date.' +1 month')).' -1 day'));
        $raw_data = DB::table('user_bills')->where([
                ['type','=','pay'],
                ['created_at','>=',$start_date],
                ['created_at','<=',$end_date],
        ])->sum('amount');
        $res = $raw_data;

        return $res;
    }

    /**
     * 獲取充值人數
     * @param $date
     * @return mixed
     */
    public static function getRechargeNum($date) {
        $start_date=$date.'00:00:00';
        $end_date = date('Y-m-d 23:59:59',strtotime(date("Y-m-01",strtotime($date.' +1 month')).' -1 day'));
        $raw_data = DB::select("SELECT COUNT(DISTINCT(uid)) as cid FROM `user_bills` WHERE created_at>='".$start_date."' AND created_at<='".$end_date."'");
        $res = $raw_data[0]->cid;

        return $res;
    }

    /**
     *
     * @param $date
     * @return mixed
     */
    public static function getBeforeRechargeSum($date) {
        $start_date=$date.'00:00:00';
        $end_date = date('Y-m-d 23:59:59',strtotime(date("Y-m-01",strtotime($date.' +1 month')).' -1 day'));
        $raw_data = DB::select("SELECT SUM(ub.amount) as rechargesum FROM `user_bills` ub 
 INNER JOIN `orders` o ON o.id = ub.order_id 
 INNER JOIN `tasks` t ON t.id = o.task_id 
 WHERE t.pay_type = 'BEFORE' AND ub.type = 'Pay'
 AND ub.created_at>='".$start_date."' AND ub.created_at<='".$end_date."'");
        $res = $raw_data[0]->rechargesum;

        return $res;
    }

    /**
     *
     * @param $date
     * @return mixed
     */
    public static function getBeforeRechargeNum($date) {
        $start_date=$date.'00:00:00';
        $end_date = date('Y-m-d 23:59:59',strtotime(date("Y-m-01",strtotime($date.' +1 month')).' -1 day'));
        $raw_data = DB::select("SELECT COUNT(distinct ub.uid) as rechargenum FROM `user_bills` ub 
 INNER JOIN `orders` o ON o.id = ub.order_id 
 INNER JOIN `tasks` t ON t.id = o.task_id 
 WHERE t.pay_type = 'BEFORE' AND ub.type = 'Pay'
 AND ub.created_at>='".$start_date."' AND ub.created_at<='". $end_date."'");
        $res = $raw_data[0]->rechargenum;

        return $res;
    }

    /**
     * 获取独立访客
     * @param $date
     * @return mixed
     */
    public static function getUV($date){

        $res = Redis::scard(date('Ymd').'UV');

        return $res;
    }

    /**
     * 获取PV
     * @param $date
     * @return string
     */
    public static function getPV($date){

        $res = Redis::hget('DailyPV',date('Ymd'));

        return $res;
    }


    public static function getRewardTimes($date){
        $start_date=$date.'00:00:00';
        $end_date = date('Y-m-d 23:59:59',strtotime(date("Y-m-01",strtotime($date.' +1 month')).' -1 day'));
        $count = DB::table('self_reco_suc_chains as srsc')
            ->join('tasks as t', 't.id', '=', 'srsc.task_id')
            ->select(DB::raw("count('srsc.id') as rewards"))
            ->where([
                ['srsc.pay_status','=','SUC_REWARD'],
                ['srsc.created_at','>=',$start_date],
                ['srsc.created_at','<=',$end_date]
            ])
            ->first();
        $rewardTimes = $count->rewards;
        return $rewardTimes;
    }

    /**
     * @param $date
     * @return int
     */
    public static function getRewardRate($date) {
        $rewardTimes = self::getRewardTimes($date);
        $selfRecoViewTimes = self::getSelfRecoView($date);
        $rewardRate = $selfRecoViewTimes>0?intval(($rewardTimes/$selfRecoViewTimes)*100):0;
        return $rewardRate;
    }

    /**
     * @param $date
     * @return string
     */
    public static function getSelfRecoView($date) {
        $start_date=$date.'00:00:00';
        $end_date = date('Y-m-d 23:59:59',strtotime(date("Y-m-01",strtotime($date.' +1 month')).' -1 day'));
        $readInfo = DB::table('self_recommendations')
            ->select(DB::raw("count(id) as cid"))
            ->where([
                ['is_view','=',1],
                ['view_time','>=',$start_date],
                ['view_time','<=',$end_date]
            ])
            ->first();
        if($readInfo){
            $readCount = $readInfo->cid;
            return $readCount;
        }
        return '';
    }

    /**
     * @param $date
     * @return mixed
     */
    public static function getHandleDuration($date) {
        $start_date=$date.'00:00:00';
        $end_date = date('Y-m-d 23:59:59',strtotime(date("Y-m-01",strtotime($date.' +1 month')).' -1 day'));
        $sql = "select unix_timestamp(sr.view_time)-unix_timestamp(sr.created_at) as diff_time,sr.id from `self_recommendations` as sr inner join `tasks` ts on ts.id = sr.task_id where sr.`is_view` = 1 AND sr.`view_time` >= '".$start_date."' AND sr.`view_time` <= '".$end_date."'";
        $res=DB::table(DB::raw("($sql) as tb_main"))
            ->select(DB::raw("sum(tb_main.diff_time) as sum_diff_time"))
            ->first();
        $handleTime = $res->sum_diff_time;
        return $handleTime;
    }

    /**
     * @param $date
     * @return mixed
     */
    public static function getSelfRecoTimes($date){
        $start_date=$date.'00:00:00';
        $end_date = date('Y-m-d 23:59:59',strtotime(date("Y-m-01",strtotime($date.' +1 month')).' -1 day'));
        $readInfo = DB::table('self_recommendations')
            ->select(DB::raw("count(id) as cid"))
            ->where([
                ['created_at','>=',$start_date],
                ['created_at','<=',$end_date]
            ])
            ->first();
        if($readInfo){
            $readCount = $readInfo->cid;
        }
        return $readCount;
    }

    /**
     * @param $date
     * @return int
     */
    public static function selfRecoSuccess($date) {
        $start_date=$date.'00:00:00';
        $end_date = date('Y-m-d 23:59:59',strtotime(date("Y-m-01",strtotime($date.' +1 month')).' -1 day'));
        $readInfo = DB::table('self_recommendations')
            ->select(DB::raw("count(id) as cid"))
            ->where([
                ['status','=','SUCCESS'],
                ['created_at','>=',$start_date],
                ['created_at','<=',$end_date]
            ])
            ->first();
        $successCount = 0;
        if($readInfo){
            $successCount = $readInfo->cid;
        }
        return $successCount;
    }

    /**
     * @param $date
     * @return int
     */
    public static function getRecoViewRate($date) {
        $RecoTotal = self::getSelfRecoTimes($date);
        $RecoViewTotal = self::getSelfRecoView($date);
        return $RecoTotal>0?intval(($RecoViewTotal/$RecoTotal)*100):0;
    }

    /**
     * @param $date
     * @return mixed
     */
    public static function getRewardSum($date) {
        $start_date=$date.'00:00:00';
        $end_date = date('Y-m-d 23:59:59',strtotime(date("Y-m-01",strtotime($date.' +1 month')).' -1 day'));
        $count = DB::table('self_reco_suc_chains as srsc')
            ->join('tasks as t', 't.id', '=', 'srsc.task_id')
            ->select(DB::raw("sum(srsc.fee) as total_rewards"))
            ->where([
                ['srsc.pay_status','=','SUC_REWARD'],
                ['srsc.created_at','>=',$start_date],
                ['srsc.created_at','<=',$end_date]
            ])
            ->first();
        $rewardTotal = $count->total_rewards;

        return $rewardTotal;
    }

    /**
     * @param $date
     * @return int
     */
    public static function getAverageRewards($date) {
        $rewards_to_people_num = DB::select("select count(id) as cid from `reward_details` where `created_at` >= '".$date.' 00:00:00'."' AND `created_at` <= '".$date.' 23:59:59'."'");
        $rewards_to_people_num = $rewards_to_people_num[0]->cid;
        $rewards_total = self::getRewardSum($date);
        return $rewards_to_people_num>0?intval($rewards_total/$rewards_to_people_num):0 ;
    }



    public static function getAllStatistic($date){
        return array(
            'share_num'=>self::getDayShareNum($date),
            'view_num'=>self::getPV($date),
            'task_num'=>self::getTaskNnum($date),
            'processing_task'=>self::getProcTask($date),
            'recharge_sum'=>self::getRechargeSum($date),
            'recharge_num'=>self::getRechargeNum($date),
            'recharge_before_sum'=>self::getBeforeRechargeSum($date),
            'recharge_before_num'=>self::getBeforeRechargeNum($date),
            'register_num'=>self::getRegisterNum($date),
            'access_num'=>self::getUV($date),
            'reward_rate'=>self::getRewardRate($date),
            'handle_time'=>self::getHandleDuration($date),
            'reco_times'=>self::getSelfRecoTimes($date),
            'reco_view_times'=>self::getSelfRecoView($date),
            'reco_suc_times'=>self::selfRecoSuccess($date),
            'reco_view_rate'=>self::getRecoViewRate($date),
            'reward_average'=>self::getAverageRewards($date),
            'reward_sum'=>self::getRewardSum($date)
        );
    }

    public static function setAllstatistic($date){
        $data = array(
            'month'=>$date,
            'share_num'=>self::getDayShareNum($date),
            'view_num'=>self::getPV($date),
            'task_num'=>self::getTaskNnum($date),
            'processing_task'=>self::getProcTask($date),
            'recharge_sum'=>self::getRechargeSum($date),
            'recharge_num'=>self::getRechargeNum($date),
            'recharge_before_sum'=>self::getBeforeRechargeSum($date),
            'recharge_before_num'=>self::getBeforeRechargeNum($date),
            'register_num'=>self::getRegisterNum($date),
            'access_num'=>self::getUV($date),
            'reward_rate'=>self::getRewardRate($date),
            'handle_time'=>self::getHandleDuration($date),
            'reco_times'=>self::getSelfRecoTimes($date),
            'reco_view_times'=>self::getSelfRecoView($date),
            'reco_suc_times'=>self::selfRecoSuccess($date),
            'reco_view_rate'=>self::getRecoViewRate($date),
            'reward_average'=>self::getAverageRewards($date),
            'reward_sum'=>self::getRewardSum($date)
        );
        self::create($data);
    }
}